package com.audaque.encoding.util;


import cn.hutool.core.util.RandomUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.pinyin.PinyinUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelWriter;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * <p>excel处理工具</p>
 *
 * @author 谭黄
 * @since 2024/3/19
 */
public class ExcelUtil extends cn.hutool.poi.excel.ExcelUtil {


    /**
     * excel合并
     *
     * @param bookBytes 多个excel
     * @return 合并的excel
     */
    public static byte[] merge(List<byte[]> bookBytes) {
        ExcelWriter writer = new ExcelWriter();
        writer.disableDefaultStyle();
        for (int i = 0; i < bookBytes.size(); i++) {
            ExcelReader reader = ExcelUtil.getReader(new ByteArrayInputStream(bookBytes.get(i)));
            List<List<Object>> read = reader.read();
            if (i == 0) {
                writer.writeRow(read.get(0));
            }
            read.remove(0);
            writer.write(read, false);
        }
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        writer.flush(out);
        writer.close();
        return out.toByteArray();

    }

    /**
     * excel获取对应表得字段名
     *
     * @param bookByte
     * @return k是字段名 v是注释
     */
    public static Map<String, String> excel2TableColumn(byte[] bookByte) {
        ExcelReader reader = ExcelUtil.getReader(new ByteArrayInputStream(bookByte));
        List<List<Object>> read = reader.read();
        List<Object> objects = read.get(0);
        Map<String, String> table = new LinkedHashMap<>();
        for (Object object : objects) {
            String row = object.toString();
            String name = PinyinUtil.getPinyin(replace(row), "");
            if (table.containsKey(name) || StrUtil.isBlank(row)) {
                //随机4位数,避免字段重名冲突,或者表头为空字符
                int c = RandomUtil.randomInt(1000, 9999);
                name = name + "_" + c;
            }
            table.put(name, row);
        }
        return table;
    }

    /**
     * 获取数据行
     *
     * @param bookByte
     * @return
     */
    public static List<List<String>> excel2RowData(byte[] bookByte) {
        ExcelReader reader = ExcelUtil.getReader(new ByteArrayInputStream(bookByte));
        List<List<Object>> read = reader.read();
        read.remove(0);
        List<List<String>> row = read.stream()
                .map(innerList -> innerList.stream()
                        .map(Object::toString) // 将每个元素转换为字符串
                        .collect(Collectors.toList()))
                .collect(Collectors.toList());
        return row;
    }

    /**
     * @param tableName 表名
     * @param columnMap 字段名
     */
    public static String createTableSql(String tableName, Map<String, String> columnMap) {
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("CREATE TABLE ").append(tableName).append(" (insert_id INT AUTO_INCREMENT PRIMARY KEY,insert_time DATETIME DEFAULT CURRENT_TIMESTAMP,");

        for (Map.Entry<String, String> entry : columnMap.entrySet()) {
            String columnName = entry.getKey();
            String columnComment = entry.getValue();

            sqlBuilder.append(columnName).append(" VARCHAR(255) COMMENT '").append(columnComment).append("', ");
        }

        // 删除最后一个逗号和空格
        sqlBuilder.delete(sqlBuilder.length() - 2, sqlBuilder.length());

        sqlBuilder.append(")");

        String createTableSQL = sqlBuilder.toString();
        return createTableSQL;
    }

    /**
     * excel分片
     *
     * @param bookByte  excel输入流
     * @param threshold 分片阈值大小(M)
     */
    public static List<byte[]> sharding(byte[] bookByte, int threshold) throws IOException {
        int size = bookByte.length;
        int thresholdByte = threshold << 20;
        if (size <= thresholdByte) {
            return List.of(bookByte);
        }
        //分片数量
        int num = (size / thresholdByte) + (size % thresholdByte > 0 ? 2 : 1);
        ExcelReader reader = ExcelUtil.getReader(new ByteArrayInputStream(bookByte));
        List<List<Object>> data = reader.read();
        List<Object> head = data.get(0);
        data.remove(0);
        List<List<List<Object>>> shardingData = splitList(data, num);
        List<byte[]> list = new ArrayList<>();

        for (int i = 0; i < shardingData.size(); i++) {
            ExcelWriter writer = new ExcelWriter();
            writer.disableDefaultStyle();
            writer.writeRow(head);
            writer.write(shardingData.get(i), false);
            ByteArrayOutputStream out = new ByteArrayOutputStream();
            writer.flush(out);
            writer.close();
            list.add(out.toByteArray());
        }
        return list;
    }

    /**
     * list分片
     *
     * @param originalList 原始列表
     * @param parts        分片数量
     * @param <T>
     * @return
     */
    private static <T> List<List<T>> splitList(List<T> originalList, int parts) {
        List<List<T>> splitLists = new ArrayList<>();
        int chunkSize = originalList.size() / parts;
        int remainder = originalList.size() % parts;
        int fromIndex = 0;
        for (int i = 0; i < parts; i++) {
            int toIndex = fromIndex + chunkSize + (i < remainder ? 1 : 0);
            splitLists.add(new ArrayList<>(originalList.subList(fromIndex, toIndex)));
            fromIndex = toIndex;
        }
        return splitLists;
    }


    static String regEx = "[\n`~!@#$%^&*()+=\\-_|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。， 、？]";

    public static String replace(String str) {
        return str.replaceAll(regEx,"").trim();
    }

}
